﻿using System;
using System.Collections.Generic;
using System.Text;

using System.Data.SqlClient;
using System.Data;
using System.Collections;
using System.Web;

namespace lxwmvc
{
    public static class sqlhelper
    {
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        public static string connectstring = System.Configuration.ConfigurationManager.AppSettings["connectstring"].ToString();

        /// <summary>
        /// 缓存表结构
        /// </summary>
        static Hashtable cachetable = Hashtable.Synchronized(new Hashtable());
        /// <summary>
        /// 单独给外部的方法，不存在事务
        /// </summary>
        /// <param name="commandText"></param>
        /// <returns></returns>
        public static DataSet out_getds(string commandText, string connectstring = "")
        {
            if (connectstring == "") connectstring = sqlhelper.connectstring;
            using (SqlConnection conn = new SqlConnection(connectstring))
            {
                if (conn.State != ConnectionState.Open) conn.Open();
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandText = commandText;
                    cmd.CommandType = CommandType.Text;

                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();

                        // Fill the DataSet using default values for DataTable names, etc
                        da.Fill(ds);

                        // Detach the OracleParameters from the command object, so they can be used again
                        cmd.Parameters.Clear();

                        return ds;
                    }
                }
            }
        }

        /// <summary>
        /// 外部执行代码
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="connectstring"></param>
        /// <returns></returns>
        public static void out_exec(string commandText, string connectstring = "")
        {
            if (connectstring == "") connectstring = sqlhelper.connectstring;
            using (SqlConnection conn = new SqlConnection(connectstring))
            {
                if (conn.State != ConnectionState.Open) conn.Open();
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandText = commandText;
                    cmd.CommandType = CommandType.Text;

                    cmd.ExecuteNonQuery();
                }
            }
        }


        public static object executescalar(string commandText)
        {
            try
            {
                SqlConnection conn = dbconnect();

                SqlCommand cmd = new SqlCommand();

                cmd.Connection = conn;
                cmd.CommandText = commandText;
                cmd.CommandType = CommandType.Text;

                begintransaction(cmd);
                return cmd.ExecuteScalar();
            }
            catch
            {
                throw new Exception("executenonquery:" + commandText);
            }
        }

        /// <summary>
        /// 增加空白行
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="count"></param>
        public static void addrows(object o, int count)
        {
            if (o.GetType() == typeof(DataTable))
            {
                var dt = o as DataTable;
                while (dt.Rows.Count < count)
                    dt.Rows.Add(dt.NewRow());
            }
            if (o.GetType() == typeof(DataRowCollection))
            {
                var drs = o as DataRowCollection;
                while (drs.Count < count)
                    drs.Add(new object[]{});
            }
        }

        public static object procedureexecutescalar(string pname)
        {
            SqlConnection conn = dbconnect();
            SqlCommand cmd = new SqlCommand();

            cmd.Connection = conn;
            cmd.CommandText = pname;
            cmd.CommandType = CommandType.StoredProcedure;

            begintransaction(cmd);
            return cmd.ExecuteScalar();
        }

        /// <summary>
        /// 输出一个table到页面
        /// </summary>
        /// <param name="pb"></param>
        /// <param name="val"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataTable responsetable(pagebase pb, string val, string sql)
        {
            DataTable dt = executedataset(sql).Tables[0];
            pb[val] = dt;
            return dt;
        }

        /// <summary>
        /// 输出一个datarow到页面。
        /// </summary>
        /// <param name="pb"></param>
        /// <param name="sql"></param>
        /// <param name="qianzui"></param>
        /// <returns></returns>
        public static DataSet responseonesql(pagebase pb, string sql, string qianzui = "")
        {
            DataSet ds = sqlhelper.executedataset(sql);
            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
                {
                    if(qianzui.Length > 0)
                        pb[qianzui + "_" + ds.Tables[0].Columns[i].ColumnName.ToLower()] = ds.Tables[0].Rows[0][i];
                    else
                        pb[ds.Tables[0].Columns[i].ColumnName.ToLower()] = ds.Tables[0].Rows[0][i];
                }
            }

            return ds;
        }

        /// <summary>
        /// 重构一条语句
        /// </summary>
        /// <param name="sql">输出has队列的sql语句，只输出第一行</param>
        /// <param name="qianzui"></param>
        /// <returns></returns>
        public static Hashtable responseonesql(string sql, string qianzui = "")
        {
            Hashtable has = new Hashtable();
            DataSet ds = sqlhelper.executedataset(sql);
            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
                {
                    if (qianzui.Length > 0)
                        has[qianzui + "_" + ds.Tables[0].Columns[i].ColumnName.ToLower()] = ds.Tables[0].Rows[0][i];
                    else
                        has[ds.Tables[0].Columns[i].ColumnName.ToLower()] = ds.Tables[0].Rows[0][i];
                }
            }

            return has;
        }


        public static Hashtable gethash(DataRow dr, string qianzui = "")
        {
            Hashtable has = new Hashtable();
            for (int i = 0; i < dr.Table.Columns.Count; i++)
            {
                if (qianzui.Length > 0)
                    has[qianzui + "_" + dr.Table.Columns[i].ColumnName.ToLower()] = dr[i];
                else
                    has[dr.Table.Columns[i].ColumnName.ToLower()] = dr[i];  
            }

            return has;
        }



        /// <summary>
        /// 一次请求 就一完整的sql语句
        /// </summary>
        public static string session_connect = "session_connect";
        static SqlConnection dbconnect()
        {
            if (HttpContext.Current.Session[session_connect] != null)
                return (SqlConnection)HttpContext.Current.Session[session_connect];
            else
            {
                SqlConnection conn = new SqlConnection(connectstring);
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                HttpContext.Current.Session[session_connect] = conn;

                return conn;
            }
        }
        /// <summary>
        /// 当前用户的session
        /// </summary>
        public static string session_transaction = "session_transaction";
        public static string has_transaction = "has_transaction";

        static void begintransaction(SqlCommand command)
        {
            //如果不存在，就不用事务了。
            if (HttpContext.Current.Session[has_transaction] == null) return;
            transAttribute tran = (transAttribute)HttpContext.Current.Session[has_transaction];
            var transaction = HttpContext.Current.Session[session_transaction];
            if (transaction != null)
            {              
                SqlTransaction sqltransaction = (SqlTransaction)transaction;
                //if (sqltransaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
                command.Transaction = sqltransaction;
            }
            else
            {
                //todo:到底用什么锁？
                SqlTransaction sqltransaction = command.Connection.BeginTransaction(tran.iso);
                //sqltransaction.Connection = command.Connection;

                command.Transaction = sqltransaction;
                HttpContext.Current.Session[session_transaction] = sqltransaction;
            }
        }
        /// <summary>
        /// 关闭数据源
        /// </summary>
        public static void dbconnectclose()
        {
            if (HttpContext.Current.Session[session_connect] != null)
            {
                SqlConnection conn = (SqlConnection)HttpContext.Current.Session[session_connect];
                if (conn != null && conn.State != ConnectionState.Closed)
                    conn.Close();
            }
        }

        /// <summary>
        /// 提交事务
        /// </summary>
        public static void transactioncommit()
        {
            if (HttpContext.Current.Session[has_transaction].tostr().Length == 0)
            {
                dbconnectclose();
                return;
            }
            var transaction = HttpContext.Current.Session[session_transaction];
            if (transaction != null)
            {
                SqlTransaction sqltransaction = (SqlTransaction)transaction;
                sqltransaction.Commit();

                HttpContext.Current.Session[session_transaction] = null;
            }
            dbconnectclose();
        }

        /// <summary>
        /// 回滚事务
        /// </summary>
        public static void transactionrollback()
        {
            if (HttpContext.Current.Session[has_transaction].tostr().Length == 0)
            {
                dbconnectclose();
                return;
            }
            var transaction = HttpContext.Current.Session[session_transaction];
            if (transaction != null)
            {
                SqlTransaction sqltransaction = (SqlTransaction)transaction;
                sqltransaction.Rollback();

                HttpContext.Current.Session[session_transaction] = null;
            }
            dbconnectclose();
        }

        /// <summary>
        /// 返回rows
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataRowCollection executedatarows(string sql)
        {
            return executedataset(sql).Tables[0].Rows;
        }

        /// <summary>
        /// 执行sql语句并返回dataset
        /// </summary>
        /// <param name="commandText"></param>
        /// <returns></returns>
        public static DataSet executedataset(string commandText)
        {
            try
            {

            SqlConnection conn = dbconnect();

            SqlCommand cmd = new SqlCommand();

            cmd.Connection = conn;
            cmd.CommandText = commandText;
            cmd.CommandType = CommandType.Text;
            begintransaction(cmd);
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                DataSet ds = new DataSet();

                // Fill the DataSet using default values for DataTable names, etc
                da.Fill(ds);

                // Detach the OracleParameters from the command object, so they can be used again
                cmd.Parameters.Clear();

                return ds;
            }
                }
            catch
            {
                throw new Exception("executenonquery:" + commandText);
            }
        }

        /// <summary>
        /// 执行存储过程并返回dataset
        /// </summary>
        /// <param name="pname"></param>
        /// <returns></returns>
        public static DataSet procedureexecutedataset(string pname)
        {

                SqlConnection conn = dbconnect();

                SqlCommand cmd = new SqlCommand();

                cmd.Connection = conn;
                cmd.CommandText = pname;
                cmd.CommandType = CommandType.StoredProcedure;

                begintransaction(cmd);
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();

                    // Fill the DataSet using default values for DataTable names, etc
                    da.Fill(ds);

                    // Detach the OracleParameters from the command object, so they can be used again
                    cmd.Parameters.Clear();

                    return ds;
                }
        }

        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="commandText"></param>
        /// <returns></returns>
        public static int executenonquery(string commandText)
        {
            try
            {
                SqlConnection conn = dbconnect();

                SqlCommand cmd = new SqlCommand();

                cmd.Connection = conn;
                cmd.CommandText = commandText;
                cmd.CommandType = CommandType.Text;
                begintransaction(cmd);
                return cmd.ExecuteNonQuery();
            }
            catch
            {
                throw new Exception("executenonquery:" + commandText);
            }
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="pname"></param>
        /// <returns></returns>
        public static int procedureexecutenonquery(string pname)
        {
            SqlConnection conn = dbconnect();

            SqlCommand cmd = new SqlCommand();

            cmd.Connection = conn;
            cmd.CommandText = pname;
            cmd.CommandType = CommandType.StoredProcedure;
            begintransaction(cmd);
            return cmd.ExecuteNonQuery();
        }

        #region 
        /// <summary>
        /// 参数设置
        /// </summary>
        /// <param name="command"></param>
        /// <param name="commandParameters"></param>
        private static void attachparameters(SqlCommand command, SqlParameter[] commandParameters)
        {
            if (command == null) throw new ArgumentNullException("command");
            if (commandParameters != null)
            {
                foreach (SqlParameter p in commandParameters)
                {
                    if (p != null)
                    {
                        // Check for derived output value with no value assigned
                        if ((p.Direction == ParameterDirection.InputOutput ||
                            p.Direction == ParameterDirection.Input) &&
                            (p.Value == null))
                        {
                            p.Value = DBNull.Value;
                        }
                        command.Parameters.Add(p);
                    }
                }
            }
        }
        /// <summary>
        /// 过滤非法字符
        /// </summary>
        static void addparams(IList lstParam, string strKey, SqlDbType sqlDbType, object objValue)
        {
            if (!strKey.StartsWith("@")) strKey = strKey.Insert(0, "@");

            SqlParameter sp = new SqlParameter(strKey, sqlDbType);
            sp.Value = objValue;
            lstParam.Add(sp);
        }


        static string CommondName = "PAGER_MSSQL";
        static string Const_PageNow = "@PageNow";
        static string Const_PageSize = "@PageSize";
        static string Const_RecordCount = "@RecordCount";
        /// <summary>
        /// 实现MSSQL分页 
        /// </summary>
        /// <param name="controller"></param>
        /// <param name="strtablename"></param>
        /// <param name="strfileds"></param>
        /// <param name="strorderfileds"></param>
        /// <param name="ipageindex"></param>
        /// <param name="ipagesize"></param>
        /// <param name="strwhere"></param>
        /// <returns></returns>
        public static pageinfo showpager(pagebase pb, string strtablename, string strfileds, string strorderfileds, int ipageindex, int ipagesize, string strwhere)
        {
            pageinfo pageinfo = new pageinfo();

            if (string.IsNullOrEmpty(strorderfileds)) throw new Exception("必须定义排序字段!");

            IList lstParam = new ArrayList();
            addparams(lstParam, "tableName", SqlDbType.VarChar, strtablename);
            addparams(lstParam, "fileds", SqlDbType.VarChar, strfileds);
            addparams(lstParam, "orderFileds", SqlDbType.VarChar, strorderfileds);
            addparams(lstParam, "strWhere", SqlDbType.VarChar, strwhere);

            int len = lstParam.Count;
            SqlParameter[] param = new SqlParameter[len + 3];

            if (len > 0) for (int i = 0; i < lstParam.Count; i++) param[i] = (SqlParameter)lstParam[i];

            ipagesize = (ipagesize == 0 ? pageinfo.pagesize : ipagesize);
            ipageindex = Math.Max(ipageindex, 1);

            param[len] = new SqlParameter(Const_PageNow, SqlDbType.Int, 4); param[len].Value = ipageindex;
            param[len + 1] = new SqlParameter(Const_PageSize, SqlDbType.Int, 4); param[len + 1].Value = ipagesize;
            param[len + 2] = new SqlParameter(Const_RecordCount, SqlDbType.Int, 4); param[len + 2].Direction = ParameterDirection.Output; param[len + 2].Value = 0;


            SqlConnection conn = dbconnect();

            SqlCommand cmd = new SqlCommand();

            cmd.Connection = conn;
            cmd.CommandText = CommondName;
            cmd.CommandType = CommandType.StoredProcedure;

            attachparameters(cmd, param);
            begintransaction(cmd);
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                DataSet ds = new DataSet();

                da.Fill(ds);
                cmd.Parameters.Clear();

                pageinfo.data = ds;
            }

            pageinfo.recordcount = (int)param[len + 2].Value;
            pageinfo.setfirstindex(ipageindex, ipagesize);

            //这里名称写死了。
            pb["pager"] = pageinfo;

            pb.dopager();

            return pageinfo;
        }

        /// <summary>
        /// 临时的，只是为了那一天使用
        /// </summary>
        /// <param name="pb"></param>
        /// <param name="strtablename"></param>
        /// <param name="strfileds"></param>
        /// <param name="strorderfileds"></param>
        /// <param name="istart"></param>
        /// <param name="inum"></param>
        /// <param name="strwhere"></param>
        /// <returns></returns>
        public static pageinfo showpagerxml(pagebase pb, string strtablename, string strfileds, string strorderfileds, int istart, int inum, string strwhere)
        {
            pageinfo pageinfo = new pageinfo();

            IList lstParam = new ArrayList();
            addparams(lstParam, "tableName", SqlDbType.VarChar, strtablename);
            addparams(lstParam, "fileds", SqlDbType.VarChar, strfileds);
            addparams(lstParam, "orderFileds", SqlDbType.VarChar, strorderfileds);
            addparams(lstParam, "strWhere", SqlDbType.VarChar, strwhere);

            int len = lstParam.Count;
            SqlParameter[] param = new SqlParameter[len + 3];

            if (len > 0) for (int i = 0; i < lstParam.Count; i++) param[i] = (SqlParameter)lstParam[i];

           

            param[len] = new SqlParameter("start", SqlDbType.Int, 4); param[len].Value = istart;
            param[len + 1] = new SqlParameter("num", SqlDbType.Int, 4); param[len + 1].Value = inum;
            param[len + 2] = new SqlParameter(Const_RecordCount, SqlDbType.Int, 4); param[len + 2].Direction = ParameterDirection.Output; param[len + 2].Value = 0;


            SqlConnection conn = dbconnect();

            SqlCommand cmd = new SqlCommand();

            cmd.Connection = conn;
            cmd.CommandText = "XML_MSSQL";
            cmd.CommandType = CommandType.StoredProcedure;

            attachparameters(cmd, param);
            begintransaction(cmd);
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                DataSet ds = new DataSet();

                da.Fill(ds);
                cmd.Parameters.Clear();

                pageinfo.data = ds;
            }

            pageinfo.recordcount = (int)param[len + 2].Value;
            //pageinfo.setfirstindex(ipageindex, ipagesize);

            //这里名称写死了。
            //pb["pager"] = pageinfo;

            //pb.dopager();

            return pageinfo;
        }
        #endregion

        /// <summary>
        /// 唯一性判断
        /// </summary>
        /// <param name="table"></param>
        /// <param name="column"></param>
        /// <param name="value"></param>
        /// <param name="msg"></param>
        /// <param name="keycolumn"></param>
        /// <param name="keyvalue"></param>
        /// <param name="where"></param>
        public static void weiyixing(string table, string column, string value, string msg, string keycolumn = "", string keyvalue = "", string where = "")
        {
            string sql = "select count(1) from {0} where {1}='{2}' {3} {4}";

            if (where != "") where = " and " + where;

            if (string.IsNullOrEmpty(keycolumn) || string.IsNullOrEmpty(keyvalue))
                sql = string.Format(sql, table, column, value, "", where);
            else
                sql = string.Format(sql, table, column, value, " and " + keycolumn + "<>" + keyvalue, where);

            int count = sqlhelper.executescalar(sql).toint32();
            if (count > 0) throw new Exception(msg);

        }


        /// <summary>
        /// 保存和更新表 返回主键
        /// </summary>
        /// <param name="pb"></param>
        /// <param name="key"></param>
        /// <param name="tablename"></param>
        public static result savetable(pagebase pb, string key, string tablename = "", Action<Hashtable> insert = null, Action<Hashtable> update = null)
        {
            if (string.IsNullOrEmpty(tablename)) tablename = key.Substring(0, key.Length - 2);//去掉ID

            result result = new lxwmvc.result();
            //int nvarchar() ntext datetime decimal null,都允许为空
            //string sql = "select * from [" + tablename + "] where 1<>1";
            //DataTable dt = executedataset(sql).Tables[0];

            int keyValue = pb.v_i(key).toint32(-1);
            Hashtable has = pb.getparams();

            key = key.ToLower();
            //给一些未赋值的变量赋值
            if (keyValue > 0)
            {
                //如果是更新,主要处理checkbox
                string keys = pb.v_s("_lxw_column_checkbox_").ToLower();
                foreach (string s in keys.Split(','))
                {
                    if (s.Length == 0) continue;
                    if (has.Contains(s))  //这里是写死了，如果允许，就是1，不允许就是0，只有两种状态，是否允许再数据库中配置一下？
                        has[s] = has[s].tostr() == "on" ? 1 : 0;
                    else
                        has[s] = 0;
                }

                if (update != null) update(has);
            }
            else
            {
                //动态赋值
                string keys = pb.v_s("_lxw_column_keys_").ToLower();
                string defaultvalue = pb.v_s("_lxw_column_default_value_").ToLower();
                string checkboxs = pb.v_s("_lxw_column_checkbox_").ToLower();
                string datetimes = pb.v_s("_lxw_column_datetime_").ToLower();

                if (keys.Length > 0)
                {
                    foreach (string s0 in keys.Split(','))
                    {
                        //去掉前面的_
                        if (string.IsNullOrEmpty(s0)) continue;
                        string s = s0.TrimStart(new char[] { '_' });

                        //把不符合要求的ck去掉，比如一次性传递了10个checkbo？？
                        //todo:这里是存在问题的。

                        if (stringhelper.findvalue(checkboxs, s0))
                        {
                            has[s] = has[s].tostr() == "on" ? 1 : 0;
                            continue;
                        }

                        if (stringhelper.findvalue(datetimes, s0))
                        {
                            has[s] = DateTime.Now;
                            continue;
                        }
                        //赋值
                        has[s] = requesthelper.fromurlparamsgetvaluebykey(defaultvalue, s0);
                    }
                }

                if (insert != null) insert(has);
            }

            return createorupdate(has, key, tablename);
        }


        

        /// <summary>
        /// 更新或者插入实体
        /// </summary>
        /// <param name="hashold"></param>
        /// <param name="key"></param>
        /// <param name="tablename"></param>
        /// <returns></returns>
        public static result createorupdate(Hashtable hashold, string key, string tablename = "")
        {
            //一般在更新的时候，如果取不到任何信息，更新会产生空的信息
            if (hashold == null || hashold.Count < 1) return null;

            if (string.IsNullOrEmpty(tablename)) tablename = key.Substring(0, key.Length - 2);//去掉ID
            Hashtable has = new Hashtable();
            foreach (string s in hashold.Keys)
            {
                has[s.ToLower()] = hashold[s];
            }

            

            result result = new result();

            result.has = has;
            string sql = "";
            key = key.ToLower();

            DataTable dt = null;
            if (has.ContainsKey(key) && has[key] != null && has[key].ToString().Length > 0)
            {
                result.action = action.update;
                sql = "select top 1 * from {0} where {1}={2}";
                sql = string.Format(sql, tablename, key, has[key]);
                //更新的时候，还是直接读取
                dt = executedataset(sql).Tables[0];
            }
            else
            {
                //这里是最容易出现错误的，当数据量越来越庞大的时候。
                result.action = action.insert;
                sql = "select top 1 * from {0} where 1<>1";
                sql = string.Format(sql, tablename).ToLower();
                //if (cachetable.ContainsKey(sql)) dt = (DataTable)cachetable[sql];
                //else
                {
                    dt = executedataset(sql).Tables[0];
                    //存储起来表结构
                    cachetable[sql] = dt;
                }
            }

            //DataTable dt = executedataset(sql).Tables[0];

            string updateColumns = "";
            string insertColumns = "";
            string insertValues = "";
            foreach (DataColumn d in dt.Columns)
            {
                string s = d.ColumnName.ToLower();
                //主键不要更新
                if (s == key) continue;
                //这里比较特殊 20120610
                if (s.IndexOf('=')!=-1) continue;
                //允许为空，并且集合中存在
                if (dt.Columns[s].AllowDBNull && !has.ContainsKey(s)) continue;

                //20121023 lxw
                //这句话非常好，只更新传递的值，其他值不更新
                if (result.action == action.update && !has.ContainsKey(s)) continue;

                if (dt.Columns[s].DataType == typeof(int))
                {
                    updateColumns += "," + s + "=";
                    insertColumns += "," + s + "";
                    insertValues += "," + has[s].tostr().Split('.')[0].toint32(0) + "";
                    updateColumns += "" + has[s].tostr().Split('.')[0].toint32(0) + "";
                }
                else if (dt.Columns[s].DataType == typeof(decimal))
                {
                    updateColumns += "," + s + "=";
                    insertColumns += "," + s + "";
                    insertValues += "," + has[s].todecimal(0) + "";
                    updateColumns += "" + has[s].todecimal(0) + "";
                }
                else if (dt.Columns[s].DataType == typeof(DateTime))
                {
                    try
                    {
                        //如果要删除日期，必须这样写 20140201
                        if (has[s].tostr() == "")
                        {
                            updateColumns += "," + s + "=";
                            insertColumns += "," + s + "";
                            insertValues += ",null";
                            updateColumns += "null";
                        }
                        else
                        {
                            DateTime time = has[s].todatetime("1900-1-1", true);
                            updateColumns += "," + s + "=";
                            insertColumns += "," + s + "";
                            insertValues += ",'" + time + "'";
                            updateColumns += "'" + time + "'";
                        }
                    }
                    catch { }
                }
                else
                {
                    updateColumns += "," + s + "=";
                    insertColumns += "," + s + "";
                    insertValues += ",'" + has[s].tostr().Replace("'", "''") + "'";
                    updateColumns += "'" + has[s].tostr().Replace("'", "''") + "'";
                }
            }
            string updateSql = "update {0} set {1} where {2}={3}";
            string InsertSql = "insert into {0}({1}) values({2});select @@identity";

            
            if (result.action == action.update)
            {
                //这里修改过错误
                executescalar(string.Format(updateSql, tablename, updateColumns.TrimStart(','), key, has[key].toint32(0)));
                result.identity = has[key].toint32(0);
            }
            else
            {
                if(insertValues.Length > 1) //防止插入空数据
                result.identity = sqlhelper.executescalar(string.Format(InsertSql, tablename, insertColumns.TrimStart(','), insertValues.TrimStart(','))).toint32();
            }

            return result;
        }

        /// <summary>
        /// 通过表，得到一个hash
        /// </summary>
        /// <param name="biao"></param>
        /// <param name="id"></param>
        /// <param name="value"></param>
        /// <param name="qianzui"></param>
        /// <returns></returns>
        public static Hashtable gethash(string biao, string id, object value,string qianzui = "")
        {
            string where = "";
            if (typeof(Int32) == value.GetType()) where = "  {1}=" + value;
            else if (typeof(Int64) == value.GetType()) where = "  {1}=" + value;
            else if (typeof(Int16) == value.GetType()) where = "  {1}=" + value;
            else if (typeof(decimal) == value.GetType()) where = "  {1}=" + value;
            else where = "  {1}='" + value + "'";
            string sql = "select top 1 * from {0} where " + where;

            return gethash(jiandansql.getonerow(string.Format(sql,biao,id)), qianzui);
        }
    }


    /// <summary>
    /// 返回结果
    /// </summary>
    public class result
    {
        /// <summary>
        /// 是新增还是修改
        /// </summary>
        public action action { get; set; }
        /// <summary>
        /// 返回主键
        /// </summary>
        public int identity { get; set; }
        /// <summary>
        /// 传递的键值对应
        /// </summary>
        public Hashtable has { get; set; }
    }

    /// <summary>
    /// 操作行为
    /// </summary>
    public enum action
    {
        insert,
        update
    }
}
